using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
    public class clsDataAccess
    {

        private static String SconString;
        public SqlConnection objConn;

        public clsDataAccess()
        {
            SconString = "server=" + ConfigurationSettings.AppSettings.Get("Source") + ";"
           + "uid=" + ConfigurationSettings.AppSettings["UID"] + ";"
           + "pwd=" + ConfigurationSettings.AppSettings["PWD"] + ";"
           + "database=" + ConfigurationSettings.AppSettings["Database"];
        }

        public DataSet CommandSelect(String procedurename, String[] variablename, String[] variablevalue)
        {
            try
            {
                objConn = new SqlConnection(SconString);
                objConn.Open();

                SqlCommand objCmd = new SqlCommand(procedurename, objConn);
                objCmd.CommandType = CommandType.StoredProcedure;

                if (variablename != null)
                {
                    for (int i = 0; i < variablename.Length; i++)
                    {
                        objCmd.Parameters.Add(variablename[i], variablevalue[i]);
                    }
                }

                SqlDataAdapter objDA = new SqlDataAdapter(objCmd);
                DataSet objDS = new DataSet(procedurename);
                objDA.Fill(objDS);
                objConn.Close();

                return objDS;
            }
            catch (Exception exx)
            {
                Console.WriteLine(exx.Message);
            }
            return null;
        }


        public int InsertUpdateDetete(String procedurename, String[] variablename, String[] variablevalue)
        {
            try
            {
                objConn = new SqlConnection(SconString);
                objConn.Open();

                SqlCommand objCmd = new SqlCommand(procedurename, objConn);
                objCmd.CommandType = CommandType.StoredProcedure;

                if (variablename != null)
                {
                    for (int i = 0; i < variablename.Length; i++)
                    {
                        objCmd.Parameters.Add(variablename[i], variablevalue[i]);
                    }
                }

                int effectedRows = objCmd.ExecuteNonQuery();

                return effectedRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
